****************************************************************************************************************************
Date: 09 JUNE 2015

Purpose: Adds when the 9th grade test is taken (date of test)

****************************************************************************************************************************;

*******************************************************************************

Tilføjer nu folkeskolekarakter som endnu en outcome mål 

*******************************************************************************;

libname raw4 'F:\Rawdata\702727';
libname out "K:\data\workdata\702727\xru\tmp";

 
*Henter skoledata;
data karakter2013;
set raw4.udfk2013;
run;

proc freq data=karakter2013;
table skoleaar;
run;


*Fokuserer på karakterer i 9. klasse;
data karak9kl;
set karakter2013;
if kltrin="09" then output karak9kl;
run;


proc freq data=karak9kl;
table FAGDISCIPLIN;
run; 


*Beholder kun observationer i Dansk eller Matematik;
data karak9kl_a;
set  karak9kl(rename=(opr_karakter=karakter));

if grundskolefag in ("Dansk" , "Matematik") then output karak9kl_a;
run;


data karak9kl_b;
set  karak9kl_a;

if FAGDISCIPLIN in ("Mundtlig" "Skriftlig" "Matematisk problemløsning" "Matematiske færdigheder") then output karak9kl_b;
run;


*Sletter mundtlig matematik observationerne;
data karak9kl_b;
set  karak9kl_b;

if FAGDISCIPLIN="Mundtlig" and grundskolefag="Matematik" then delete;
run;

proc freq data=karak9kl_b;
table fagkode;
run;


*deler op på års- og eksamenskarakter;
data karak9kl_c;
set karak9kl_b;

if proeveform="Standpunkt" then aarskarakter=karakter;
if proeveform in ("Prøvefag 2002-2006" "Bundne prøvefag") then eksamenskarakter=karakter;

run;

proc print data=karak9kl_c (obs=500);
var proeveform karakter aarskarakter eksamenskarakter;
run;

proc freq data=karak9kl_c;
table (FAGDISCIPLIN proeveform)*SKOLEAAR;
run;



data karak9kl_d;
set karak9kl_c;

*Dansk - aarskarakter/stdpunktskarakter;

if GRUNDSKOLEFAG="Dansk" and FAGDISCIPLIN="Mundtlig" and proeveform="Standpunkt" then Dansk_m=karakter;
if GRUNDSKOLEFAG="Dansk" and FAGDISCIPLIN="Skriftlig" and proeveform="Standpunkt" then Dansk_s=karakter;

*Matematik aarskarakter/stdpunktskarakter;

if GRUNDSKOLEFAG="Matematik" and FAGDISCIPLIN in ("Skriftlig", "Matematisk problemløsning" "Matematiske færdigheder") and proeveform="Standpunkt" then Mat_s=karakter;


*Dansk afgangsprøve/eksamenskarakter;
if GRUNDSKOLEFAG="Dansk" and FAGDISCIPLIN="Mundtlig" and proeveform in ("Prøvefag 2002-2006" "Bundne prøvefag") then Dansk_m_prove=karakter;
if GRUNDSKOLEFAG="Dansk" and FAGDISCIPLIN="Skriftlig" and proeveform in ("Prøvefag 2002-2006" "Bundne prøvefag") then Dansk_s_prove=karakter;


*Til afgangsprøve;
if GRUNDSKOLEFAG="Matematik" and FAGDISCIPLIN in ("Skriftlig", "Matematisk problemløsning" "Matematiske færdigheder") and proeveform in ("Prøvefag 2002-2006" "Bundne prøvefag") then Mat_s_prove=karakter;

run;


proc print data=karak9kl_d (obs=1000);
var Grundskolefag fagdisciplin proeveform Dansk_m Dansk_s Mat_s Dansk_m_prove Dansk_s_prove Mat_s_prove;
run;


********************
KØR som den er
********************;

*år hvor 9. klasse er afsluttet;
data karak9kl_e;
set karak9kl_d;
year2=substr(skoleaar,6,4);
year1=year2*1;
drop year2;
run;

****************************************************************************************************************

*Beregner karakter gns;
*Udvælger elevens karakterer fra den første gang eleven går i 9. klasse;

***************************************************************************************************************;

proc sort data= karak9kl_e;
by pnr year1;
run;

data karak9kl1;
set karak9kl_e;

if lag(year1)^=year1 and lag(pnr)=pnr then d=1;
run;

data karak9kl1;
set karak9kl1;
by pnr;

if first.pnr then d2=d;
else d2+d;
run;

data karak9kl1;
set karak9kl1;
if d2^=. then delete;
run;

********************************************************************************************************************
Beregner middelvædi og standardafvigelse for at kunne standardisere. De beregnes for hvert år.
****************************************************************************************************;

proc printto print='K:\data\workdata\702727\xru\Generelt\Output\outcomes.txt';
run;

*Jeg finder gennemsnitskarakteren per år;
proc sort data=karak9kl1;
by year1;
run;

*Samlet karakter;

proc sql;
create table karak1 as
select *,  mean(karakter)label="Mean karakter" as mean_karakter
from karak9kl1
group by year1;
quit;

proc print data=karak1 noobs;
run;

proc sql;
create table karak2 as
select *,  std(karakter)label="Std karakter" as std_karakter
from karak1
group by year1;
quit;

proc print data=karak2 noobs;
run;

*Årskarakter;
proc sql;
create table karak3 as
select *,  mean(aarskarakter)label="Mean aarskarakter" as mean_aarskarakter
from karak2
group by year1;
quit;

proc print data=karak3 noobs;
run;

proc sql;
create table karak4 as
select *,  std(aarskarakter)label="Std aarskarakter" as std_aarskarakter
from karak3
group by year1;
quit;

proc print data=karak4 noobs;
run;



*Prøvekarakter (eksamens karakter);
proc sql;
create table karak5 as
select *,  mean(eksamenskarakter)label="Mean eksamenskarakter" as mean_eksamenskarakter
from karak4
group by year1;
quit;

proc print data=karak5 noobs;
run;

proc sql;
create table karak6 as
select *,  std(eksamenskarakter)label="Std eksamenskarakter" as std_eksamenskarakter
from karak5
group by year1;
quit;

proc print data=karak6 noobs;
run;


*Mundtlig dansk årskarakter;
proc sql;
create table karak7 as
select *,  mean(Dansk_m)label="Mean Dansk_m" as mean_Dansk_m
from karak6
group by year1;
quit;

proc print data=karak7 noobs;
run;

proc sql;
create table karak8 as
select *,  std(Dansk_m)label="Std Dansk_m" as std_Dansk_m
from karak7
group by year1;
quit;

proc print data=karak8 noobs;
run;


*Skriftlig dansk årskarakter;
proc sql;
create table karak9 as
select *,  mean(Dansk_s)label="Mean Dansk_s" as mean_Dansk_s
from karak8
group by year1;
quit;

proc print data=karak9 noobs;
run;

proc sql;
create table karak10 as
select *,  std(Dansk_s)label="Std Dansk_s" as std_Dansk_s
from karak9
group by year1;
quit;

proc print data=karak10 noobs;
run;


*Skriftlig matematik årskarakter;
proc sql;
create table karak11 as
select *,  mean(Mat_s)label="Mean Mat_s" as mean_Mat_s
from karak10
group by year1;
quit;

proc print data=karak11 noobs;
run;

proc sql;
create table karak12 as
select *,  std(Mat_s)label="Std Mat_s" as std_Mat_s
from karak11
group by year1;
quit;

proc print data=karak12 noobs;
run;

*Mundtlig dansk eksamensskarakter;
proc sql;
create table karak13 as
select *,  mean(Dansk_m_prove)label="Mean Dansk_m_prove" as mean_Dansk_m_prove
from karak12
group by year1;
quit;

proc print data=karak13 noobs;
run;

proc sql;
create table karak14 as
select *,  std(Dansk_m_prove)label="Std Dansk_m_prove" as std_Dansk_m_prove
from karak13
group by year1;
quit;

proc print data=karak14 noobs;
run;


*Skriftlig dansk årskarakter;
proc sql;
create table karak15 as
select *,  mean(Dansk_s_prove)label="Mean Dansk_s_prove" as mean_Dansk_s_prove
from karak14
group by year1;
quit;

proc print data=karak15 noobs;
run;

proc sql;
create table karak16 as
select *,  std(Dansk_s_prove)label="Std Dansk_s_prove" as std_Dansk_s_prove
from karak15
group by year1;
quit;

proc print data=karak16 noobs;
run;


*Skriftlig matematik årskarakter;
proc sql;
create table karak17 as
select *,  mean(Mat_s_prove)label="Mean Mat_s_prove" as mean_Mat_s_prove
from karak16
group by year1;
quit;

proc print data=karak17 noobs;
run;

proc sql;
create table karak18 as
select *,  std(Mat_s_prove)label="Std Mat_s_prove" as std_Mat_s_prove
from karak17
group by year1;
quit;

proc print data=karak18 noobs;
run;

******************************************************
Afslutter printto -- default destination genoprettes
******************************************************;
proc printto;
run;


*Jeg standardiserer;

data standardisere;
set karak18;
karakter1=(karakter-mean_karakter)/std_karakter;

aarskarakter1=(aarskarakter-mean_aarskarakter)/std_aarskarakter;
eksamenskarakter1=(eksamenskarakter-mean_eksamenskarakter)/std_eksamenskarakter;

Dansk_m1=(Dansk_m-mean_Dansk_m)/std_Dansk_m;
Dansk_s1=(Dansk_s-mean_Dansk_s)/std_Dansk_s;
Mat_s1=(Mat_s-mean_Mat_s)/std_Mat_s;

Dansk_m_prove1=(Dansk_m_prove-mean_Dansk_m_prove)/std_Dansk_m_prove;
Dansk_s_prove1=(Dansk_s_prove-mean_Dansk_s_prove)/std_Dansk_s_prove;
Mat_s_prove1=(Mat_s_prove-mean_Mat_s_prove)/std_Mat_s_prove;
run;


proc means maxdec=7 data=standardisere;
class year1;
var karakter1;

run;


*Collapser for at bestemme antal børn;
data standardisere1;
set standardisere(keep=pnr year1 instnr karakter1 aarskarakter1 eksamenskarakter1
Dansk_m1 Dansk_s1 Mat_s1 Dansk_m_prove1 Dansk_s_prove1 Mat_s_prove1 rename=(instnr=i));
instnr=i*1;
run;


*Kollapser per pnr for at kunne bestemme antallet af børn;
proc means data=standardisere1 NWAY NOPRINT;
class pnr;
Output Out=standardisere_pnr mean=;
run;



*****************************************************************************************************************
Registrerer om et barn har gennemført 9. klasses afgangsprøve (dansk skriftlig og mundtlig) og matematik (skriftligt)

********************************************************************************************************************;

data afgangsprove;
set karak9kl_e(keep= pnr year1 Mat_s_prove Dansk_m_prove Dansk_s_prove);
run;

*tager middelværdi af eksamenskarakterne i afgangsprøven. De halve karakterer skyldes gns i færdighedsregning samt problem regning i mat;
proc means data=afgangsprove NWAY NOPRINT;
class pnr ;
Output Out=afgangsprove mean=;
run;

data afgangsprove;
set  afgangsprove;
afgangsprove=1;
if Mat_s_prove=. or Dansk_m_prove=. or Dansk_s_prove=. then afgangsprove=0;
keep pnr afgangsprove;
run;

* Merger afgangsprøve med standardisere_pnr;

proc sort data = standardisere_pnr;
by pnr;
run;

proc freq data = standardisere_pnr;
tables karakter1 aarskarakter1 eksamenskarakter1;
run;

proc sort data = afgangsprove;
by pnr;
run;

proc freq data = afgangsprove;
tables afgangsprove;
run;

data outcomes;
merge standardisere_pnr (in=k) afgangsprove;
by pnr;
if k;

*labels;
label karakter1="Overall mark, avr of exam and annual, standardiseret";
label aarskarakter1="Avr yearly mark, 9th grade, standardiseret";
label eksamenskarakter1="Avr exam mark, 9th gradem, standardiseret";
label afgangsprove="Completed 9th grade (0/1)";
run;

data outcomes;
set outcomes;
drop i aarskarakter1 eksamenskarakter1 Dansk_m1 Dansk_s1 Mat_s1 Dansk_s_prove1 klbetegn _TYPE_ _FREQ_;

rename year1=year_completed;
run;

data outcomes1;
set outcomes;

keep pnr year_completed;
run;

proc freq data=outcomes1;
table year_completed;
run;

*STATA datasæt;
proc export data=outcomes1 outfile="K:\Data\Workdata\702727\FBOI\Data\year_completed.dta" dbms=stata replace;
run;
